<?php

class Application_Model_DbTable_Shop extends Zend_Db_Table_Abstract
{

    protected $_name = 'Shop';
    public static $SHOP_PENDING = 0;
    public static $SHOP_ACCEPT = 1;
    public static $SHOP_REJECT = 2;

    public function addShop($name,$owner,$address,$city,$phone,$email,$url,
            $description,$item_sold,$membership){
        $data = array(
            "name" => $name,
            "owner" => $owner,
            "address" => $address,
            "city" => $city,
            "phone" => $phone,
            "email" => $email,
            "url" => $url,
            "description" => $description,
            "item_sold" => $item_sold,
            "membership_id" => $membership
        );
        $this->insert($data);
    }

    public function updateData($id,$name,$owner,$address,$city,$province,$zip_code,$phone,$email,$url,
            $description,$item_sold,$membership){
        $data = array(
            "name" => $name,
            "owner" => $owner,
            "address" => $address,
            "city" => $city,
            "province" => $province,
            "zip_code" => $zip_code,
            "phone" => $phone,
            "email" => $email,
            "url" => $url,
            "description" => $description,
            "item_sold" => $item_sold,
            "membership_id" => $membership,
            "status" => 1
        );
        $this->update($data, "shop_id = ".(int)$id);
    }

    public function getId($membership, $email, $phone){
        $row = $this->fetchRow("membership_id = ".$membership." AND email = '".$email."' AND phone = '".$phone."' AND deleted = 0");
        if(!$row) return -1;
        else return $row->shop_id;
    }

    public function getCategory($id){
        $query = $this->select();
        $query->from(array('st'=>'Shop_type'), array('category_id','shop_id'));
        $query->joinInner(array('c'=>'Category'), 'st.category_id = c.category_id', array('cat_name'=>'name'));
        $query->where('st.shop_id = '.$id);
        $query->setIntegrityCheck(false);
        return $this->fetchAll($query);
    }


    public function getKeyword($id){
        $query = $this->select();
        $query->from(array('sk'=>'Shop_keyword'), array('keyword_id','shop_id'));
        $query->joinInner(array('k'=>'Keyword'), 'sk.keyword_id = k.keyword_id', array('name'));
        $query->where('sk.shop_id = '.$id);
        $query->setIntegrityCheck(false);
        return $this->fetchAll($query);
    }
    /*
    public function getAll($where = null){
        $query = $this->select();
        $query->from(array('s'=>'Shop'));
        $query->joinInner(array('ms'=>'Membership_status'),'s.membership_id = ms.membership_id',array('ms_name'=>'name'));
        $query->setIntegrityCheck(false);
        if($where) $query->where($where);
        return $this->fetchAll($query);
    }
    */
    public function getAll($number,$page,$where = null,$order = null,$method = null){
        // start and end row to display
        $start = ($page - 1) * $number + 1;
        $end = $start + $number - 1 ;

        // WHERE clause
        $where = ($where)? " AND ".$where : "";

        // ORDER BY clause
        $order = ($order)? $order : "shop_id";
        $method = ($method)? $method : "ASC";


        $db = $this->getAdapter();
        $sql = "SELECT s.*, ms.name AS ms_name ".
            "FROM (SELECT rownumber = ROW_NUMBER() OVER (ORDER BY sh.$order $method), sh.* FROM Shop AS sh WHERE deleted = 0".$where.") as s ".
            "INNER JOIN Membership_status as ms ON s.membership_id = ms.membership_id ".
            "WHERE rownumber >= $start AND rownumber <= $end";
        $stmt = $db->query($sql);
        return $stmt->fetchAll();
    }

    public function getShop($id){
        $query = $this->select();
        $query->from(array('s'=>'Shop'));
        $query->joinInner(array('ms'=>'Membership_status'),'s.membership_id = ms.membership_id',array('ms_name'=>'name'));
        $query->where("shop_id = ".$id);
        $query->setIntegrityCheck(false);
        $shop = $this->fetchRow($query);
        return $shop->toArray();
    }

    public function updateStatus($id, $status, $reason = null){
        $data = array();
        if($reason) $data = array("status"=>$status,"reject_reason"=>$reason);
        else $data = array("status"=>$status);
        $this->update($data, "shop_id = ".(int)$id);
    }

    public function deleteShop($id){
        //Set status to 'deleted'
        $data = array("deleted"=>1);
        $this->update($data, "shop_id =". (int)$id);

        //unassign keyword and category
        $db = $this->getAdapter();
        $stmt = $db->query("DELETE FROM Shop_type WHERE shop_id = $id");
        $stmt->execute();
        $stmt = $db->query("DELETE FROM Shop_keyword WHERE shop_id = $id");
        $stmt->execute();
    }

    public function countStatus($status){
        $query = $this->select();
        $query->from(array("Shop"),array("total"=>"count(shop_id)"));
        $query->where("deleted = 0 AND status = $status");
        $result = $this->fetchRow($query);
        return $result["total"];
    }

    public function countMembership(){
        $query = $this->select();
        $query->from(array("s"=>"Shop"),array("total"=>"count(shop_id)"));
        $query->joinRight(array("ms"=>"Membership_status"), "s.membership_id = ms.membership_id",array("name"));
        $query->group("ms.name");
        $query->where("deleted = 0");
        $query->setIntegrityCheck(false);
        return $this->fetchAll($query);
    }

    public function getHighRating($number){
        $db = $this->getAdapter();
        $sql = "SELECT TOP $number shop_id,name,rating FROM Shop WHERE deleted = 0 AND status = 1 ORDER BY rating DESC";
        $stmt = $db->query($sql);
        return $stmt->fetchAll();
    }

    public function getHighHit($number){
        $db = $this->getAdapter();
        $sql = "SELECT TOP $number shop_id,name,hit FROM Shop WHERE deleted = 0 AND status = 1 ORDER BY hit DESC";
        $stmt = $db->query($sql);
        return $stmt->fetchAll();
    }

    public function countRows($where = null){
        $query = $this->select();
        $query->from(array("s"=>"Shop"),array("total"=>"count(shop_id)"));
        if($where) $query->where ("deleted = 0 AND $where");
        $row = $this->fetchRow($query);
        return $row["total"];
    }

    public function visited($id){
        $db = $this->getAdapter();
        $stmt = $db->query("UPDATE Shop SET hit = hit + 1 WHERE shop_id = $id");
        $stmt->execute();
    }

     public function pdfToko($number,$page,$where = null,$order = null,$method = null){
        // start and end row to display
       $start = ($page - 1) * $number + 1;
        $end = $start + $number - 1 ;

        // WHERE clause
        $where = ($where)? " AND ".$where : "";

        // ORDER BY clause
        $order = ($order)? $order : "shop_id";
        $method = ($method)? $method : "ASC";


        $db = $this->getAdapter();
        $sql = "SELECT s.*, ms.name AS ms_name ".
            "FROM (SELECT rownumber = ROW_NUMBER() OVER (ORDER BY sh.$order $method), sh.* FROM Shop AS sh WHERE deleted = 0".$where." ) as s ".
            "INNER JOIN Membership_status as ms ON s.membership_id = ms.membership_id ";
            "WHERE rownumber >= $start AND rownumber <= $end";
        $stmt = $db->query($sql);
        return $stmt->fetchAll();


        

    }

}

